package com.hpe.service.impl;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.hpe.pojo.Enterpriseentity;
import com.hpe.pojo.Jobentity;
import com.hpe.pojo.Offerentity;
import com.hpe.service.IStuInfoService;
import com.hpe.vo.JobInfo;
import com.hpe.vo.StuBasicInfo;
import com.hpe.vo.StuEmployInfo;

import antlr.collections.impl.LList;
import com.hpe.util.HibernateUtil;

/**
 * Created by 何琳
 * 2017/9/21 0021 下午 20:26.
 */

public class StuInfoServiceImpl implements IStuInfoService {

	@Override
	public double queryStuWPassRate(int stuid) {
		// TODO Auto-generated method stub
		int sumNum=wSumCount(stuid);
		if (sumNum==0) {
			return -1;
		}
		int passNum=wPassCount(stuid);
		return 1.0*passNum/sumNum;
	}

	@Override
	public double queryStuIPassRate(int stuid) {
		// TODO Auto-generated method stub
		int sumNum=iSumCount(stuid);
		if (sumNum==0) {
			return -1;
		}
		int passNum=iPassCount(stuid);
		return 1.0*passNum/sumNum;
	}

	//怎么算offer率
	@Override
	public double queryStuOfferRate(int stuid) {
		// TODO Auto-generated method stub
		int wSumNum=wSumCount(stuid);
		int iSumNmu=iSumCount(stuid);
		int passNum=oPassCount(stuid);
		if(wSumNum!=0||iSumNmu!=0)
		if (wSumNum>iSumNmu) {
			return 1.0*passNum/wSumNum;
		}else{
			return 1.0*passNum/iSumNmu;
		}
		return -1;
	}

	//模糊查询学生的基本信息（姓名+班级+性别+学校+专业+联系方式+住址+入学时间）
	@Override
	public List<StuBasicInfo> queryStuBasicInfo(String stuname,int pageno) {
		// TODO Auto-generated method stub
		String[] classes={"Studententity s","Schoolentity o","Clazzentity c"};
		String[] fields={"s.stuname","c.classnum","c.classtype","s.sex","o.schname","s.major","s.tel","s.address","s.time"};
		String condition="where s.classid=c.classid and s.schid=o.schid and s.stuname like '%"+stuname+"%'";
		List<Object[]> list=HibernateUtil.multiTableSelect(fields, classes, condition, pageno, 8);
		if (!list.isEmpty()) {
			List<StuBasicInfo> list2=new ArrayList<StuBasicInfo>();
			for (Object[] objects : list) {
				if (objects.length==9) {
					StuBasicInfo stuBasicInfo= new StuBasicInfo();
					stuBasicInfo.setStuname((String)objects[0]);
					stuBasicInfo.setClassnum((Integer)objects[1]);
					stuBasicInfo.setClasstype((Integer)objects[2]);
					stuBasicInfo.setSex((String)objects[3]);
					stuBasicInfo.setSchname((String)objects[4]);
					stuBasicInfo.setMajor((String)objects[5]);
					stuBasicInfo.setTel((String)objects[6]);
					stuBasicInfo.setAddress((String)objects[7]);
					stuBasicInfo.setTime((Date)objects[8]);
					list2.add(stuBasicInfo);
				}
			}
			return list2;
		}
		return null;
	}

	//查询学生招聘详情（公司名称+岗位名称+笔试结果+笔试时间+面试情况+面试时间+offer情况）
	@Override
	public List<StuEmployInfo> queryStuEmployInfo(int id) {
		// TODO Auto-generated method stub
		String hql="SELECT * from (SELECT w.jobid,w.wtime from written w where w.stuid="+id+") a LEFT JOIN (SELECT i.jobid,i.itime from interview i where i.stuid="+id+") b on a.jobid=b.jobid" 
               +" UNION "  
               +"SELECT * from (SELECT w.jobid,w.wtime from written w where w.stuid="+id+") a RIGHT JOIN (SELECT i.jobid,i.itime from interview i where i.stuid="+id+") b on a.jobid=b.jobid ";
		List list=HibernateUtil.query(hql);
		if (!list.isEmpty()) {
			List<StuEmployInfo> list2=new ArrayList<StuEmployInfo>();
			for (Object object : list) {
				Object[] objArray = (Object[]) object;
				System.out.println(objArray[1]);
				System.out.println(objArray[3]);
				StuEmployInfo stuEmployInfo= new StuEmployInfo();
				stuEmployInfo.setWstate(String.valueOf(objArray[0]));
				stuEmployInfo.setWtime((Timestamp)objArray[1]);
				stuEmployInfo.setIstate(String.valueOf(objArray[3]));
				stuEmployInfo.setItime((Timestamp)objArray[3]);
				//查询岗位名称 并返回Jobentity
				Jobentity jobentity=(Jobentity)HibernateUtil.get(Jobentity.class, (Integer)objArray[0]);
				if (jobentity!=null) {
					stuEmployInfo.setJobname(jobentity.getJobname());
					//通过企业id查企业名称
					Enterpriseentity enterpriseentity=(Enterpriseentity)HibernateUtil.get(Enterpriseentity.class, jobentity.getEnterid());
					if (enterpriseentity!=null) {
						stuEmployInfo.setEntername(enterpriseentity.getEntername());
					}
				}
				//offer情况
				List<Offerentity> list3=HibernateUtil.query("Offerentity o", "where o.jobid="+(Integer)objArray[0]+" and o.stuid="+id);
				if (!list3.isEmpty()) {
					stuEmployInfo.setOfferstate((String.valueOf(list3.get(0).getJobid())));
				}
				list2.add(stuEmployInfo);
			}
			return list2;
		}
		return null;
	}

	
	
	@Override
	public int wPassCount(int stuid) {
		// TODO Auto-generated method stub
		return HibernateUtil.recordCount("*","Writtenentity w", "where w.stuid="+stuid+" and state=1");
	}

	@Override
	public int wSumCount(int stuid) {
		// TODO Auto-generated method stub
		return HibernateUtil.recordCount("*", "Writtenentity w", "where w.stuid="+stuid);
	}

	@Override
	public int iPassCount(int stuid) {
		// TODO Auto-generated method stub
		return HibernateUtil.recordCount("*","Interviewentity i", "where i.stuid="+stuid+" and state=1");
	}

	@Override
	public int iSumCount(int stuid) {
		// TODO Auto-generated method stub
		return HibernateUtil.recordCount("*", "Interviewentity i", "where i.stuid="+stuid);
	}

	@Override
	public int oPassCount(int stuid) {
		// TODO Auto-generated method stub
		return HibernateUtil.recordCount("*","Offerentity o", "where o.stuid="+stuid);
	}

}
